﻿
CREATE PROCEDURE [dbo].[GenListaExecutieChelt]   
@idLista smallint, @CodUnitate smallint, 
@An varchar(4)
AS

DECLARE @dataUI as float, @dtDataStartUI as datetime,@dtDataEndUI as datetime
DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @strCapitol as varchar(100)
DECLARE @tabela varchar(100)
DECLARE @categorie as tinyint
DECLARE @strcategorie as varchar(10)
DECLARE @tipb as varchar(10)
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @fni bit
DECLARE @centr bit
DECLARE @idJurn smallint
DECLARE @strFiltruCapitol varchar(200)
DECLARE @strFiltruSS varchar(200)

SET @idJurn = -1
SET @categorie = 0
SET @strcategorie = '0'
SET @analitica = 0
SET @cfm = 0
SET @fni = 0
SET @centr = 0

SELECT @strDataStart=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLD' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
SET @strDataStart = LTRIM(@strDataStart)
SET @strDataStart = RTRIM(@strDataStart)
IF LEN(@strDataStart)<>0
BEGIN
	SET @dtDataStartUI = CAST(CAST(@strDataStart AS float)-2  AS datetime)
	SET @strDataStart = ''''+CAST(@dtDataStartUI AS varchar(50))+''''
END

SELECT @strDataEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLD' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
SET @strDataEnd = LTRIM(@strDataEnd)
SET @strDataEnd = RTRIM(@strDataEnd)

IF LEN(@strDataEnd)<>0
BEGIN
	SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float)-2 AS datetime)
	SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
END

SELECT @strContStart=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLC' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
SELECT @strContEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLC' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate



--conturi fara miscari
SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='CFM' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
if @tipb='1'
	SET @cfm =1

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='FNI' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
if @tipb='1'
	SET @fni =1

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='CNT' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
if @tipb='1'
	SET @centr =1

SELECT @strCapitol=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='JRN' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
SELECT @idJurn = IDJurnal FROM [Jurnale] WHERE [Codificare]=@strCapitol  AND [CodUnitate]=@CodUnitate


	
SET @strFiltruCapitol = ''
if @idJurn>0 AND @idJurn is NOT NULL
	SET @strFiltruCapitol = ' AND IDJurnal='+cast(@idJurn as varchar(10))

SET @strFiltruSS = ''
SELECT @strFiltruSS=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='SFI' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate

if LEN(RTRIM(LTRIM(@strFiltruSS)))>0 AND @idJurn is NOT NULL
	SET @strFiltruSS = ' AND (dbo.GetSursaFinantare(Cont) IN (SELECT fart.value FROM dbo.SplitParameters('''+@strFiltruSS+''','','') fart))'	



IF  EXISTS(SELECT * FROM tempdb..SysObjects WHERE name = '#balanta' AND type = 'U')	
BEGIN
	DROP TABLE  #balanta
	IF @@ERROR != 0 GOTO EROARE				
END

DECLARE @ca varchar(1000)
DECLARE @ab varchar(1000)
DECLARE @al varchar(1000)
DECLARE @det varchar(1000)
DECLARE @plati varchar(1000)
DECLARE @chelt varchar(2000)
DECLARE @toate varchar(6000)

SET @det =','
SET @plati =','
SET @chelt =','
SET @ca =','
SET @al =','
SET @ab =','

SELECT @det=@det + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CD}%' AND [CodUnitate]=@CodUnitate
SELECT @ca=@ca + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CA}%'  AND [CodUnitate]=@CodUnitate
SELECT @ab=@ab + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AB}%'  AND [CodUnitate]=@CodUnitate
SELECT @al=@al + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AL}%' AND [CodUnitate]=@CodUnitate 
SELECT @plati=@plati + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '7%'  AND [CodUnitate]=@CodUnitate
SELECT @chelt=@chelt +  Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '6%'  AND [CodUnitate]=@CodUnitate
SET @toate = @det+@chelt+@plati+@ca+@ab+@al


CREATE TABLE [#balanta_t] (
	[ArtAl] varchar(50) NOT NULL, 
	[AAParinte] varchar(50) NOT NULL, 
	[Categorii] varchar(50) NOT NULL, 
	[Denumire] varchar(400) NOT NULL DEFAULT '',
	[Anexa6] bit NOT NULL DEFAULT 0,	
	[Terminal] bit NOT NULL,
	[Titlu] varchar(100) NOT NULL,

	[CrediteAprobate] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CrediteDeschise] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[AngajamenteBugetare] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[AngajamenteLegale] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[PlatiNete] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[DisponibilCredit] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[Cheltuieli] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[DebitInitial] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CreditInitial] DECIMAL(20,2) NOT NULL DEFAULT 0
)

DECLARE @strSelect	varchar(8000)
DECLARE @filtData varchar(500)
DECLARE @filtDataC varchar(500)
DECLARE @filtC varchar(500)
DECLARE @filtCC varchar(500)
DECLARE @strCodUnitate varchar(50)
DECLARE @arh varchar(4)
DECLARE @filtruArh varchar(300)
DECLARE @filtruArhRulajeDoc varchar(300)


SET @filtData = ''
SET @filtDataC = ''
SET @filtC = ''
SET @filtCC = ''
SET @strCodUnitate = CAST(@CodUnitate AS varchar(50))

SET @arh = ''
SET @filtruArh=''
SET @filtruArhRulajeDoc=''
IF @An<>''
BEGIN
	SET @arh='arh'
	SET @filtruArh=' AND An=' + @An
	SET @filtruArhRulajeDoc=' AND D.An=' + @An
END	

IF @idLista = 1
	BEGIN
		IF LEN(@strDataEnd) <>0
			SET @filtData =  ' AND D.DataDoc <='+@strDataEnd		
	END
ELSE
	BEGIN
		IF LEN(@strDataStart) <>0
			SET @filtData =  ' AND D.DataDoc >='+@strDataStart
		IF LEN(@strDataEnd) <>0
			SET @filtData = @filtData + '  AND D.DataDoc <='+@strDataEnd	
	END
IF LEN(@strContStart) <>0
	SET @filtC =  '  AND F.ContDebit >='''+@strContStart + ''' '
IF LEN(@strContEnd) <>0
	SET @filtC = @filtC + ' AND F.ContDebit <='''+@strContEnd + ''' '

SET @filtData = @filtData + @filtC
IF @fni = 1
	SET @filtData =@filtData + '  AND F.TipDocJust <>''IC'' '  

SET @filtDataC = @filtData
SET @filtDataC = REPLACE(@filtDataC,'F.ContDebit','F.ContCredit') 
PRINT 'Total' + @filtDataC
SET @filtCC = REPLACE(@filtC,'F.ContDebit','Cont') 
SET @filtC = REPLACE(@filtC,'F.ContDebit','a.ArtAl') 

--	SUM(COALESCE(Cnt.[DebitInitial],0))  DebitInitial,
--	SUM(COALESCE(Cnt.[CreditInitial],0)) CreditInitial
--	Cnt.[DebitInitial],Cnt.[CreditInitial]

SET @strSelect =' INSERT INTO [#balanta_t]
	 SELECT a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire+a.Denumire+a.TermDenumire,a.Anexa6,a.Terminal,a.Titlu,
	SUM(COALESCE(Rul.CrediteAprobate,0)) CrediteAprobate, 
	SUM(COALESCE(Rul.CreditDeschis,0)) CrediteDeschise, 
	SUM(COALESCE(Rul.AngajamenteBugetare,0)) AngajamenteBugetare, 
	SUM(COALESCE(Rul.AngajamenteLegale+Cnt.[DebitInitial]-Cnt.[CreditInitial],0)) AngajamenteLegale, 
	SUM(COALESCE(Rul.PlatiNete,0)) PlatiNete,
	SUM(COALESCE(Rul.[CreditDeschis],0)) - SUM(COALESCE(Rul.PlatiNete,0)) DisponibilCredit,
	SUM(COALESCE(Rul.Cheltuieli,0)) Cheltuieli, 
	SUM(COALESCE(Cnt.[DebitInitial],0))  DebitInitial,
	SUM(COALESCE(Cnt.[CreditInitial],0)) CreditInitial
FROM [ArticoleAliniate] a LEFT OUTER JOIN 
(SELECT 
dbo.GetAnalitic(Cont) Cont,[CodUnitate],
sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(Cont)+'','','''+@al+''',DebitInitial)) DebitInitial,
sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(Cont)+'','','''+@al+''',CreditInitial)) CreditInitial
FROM
['+@arh+'Conturi]
WHERE Terminal = 1 AND Cont<>''N/A''  AND  dbo.IsContIn('',''+ dbo.GetSintetic(Cont)+'','','''+@toate+''')<>0 ' + @filtCC + '
 AND [CodUnitate] = '+@strCodUnitate+@filtruArh +
 @strFiltruSS + 
 'GROUP BY dbo.GetAnalitic(Cont),[CodUnitate]) CNT
ON dbo.IsContInclus(a.ArtAl,CNT.Cont,a.Terminal)=1 AND a.[CodUnitate]=CNT.[CodUnitate]
LEFT OUTER JOIN
(SELECT  
			dbo.GetAnalitic(COALESCE(t2.ContCredit, t1.ContDebit)) Cont, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@ca+''',SumaDebit)) CrediteAprobate, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@det+''',SumaDebit)) CreditDeschis, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@ab+''',SumaDebit)) AngajamenteBugetare, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@al+''',SumaDebit)) AngajamenteLegale, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@plati+''',COALESCE(SumaCredit,0)-COALESCE(SumaDebit,0))) PlatiNete, 
			sum(dbo.IsContInRetValoare('',''+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+'','','''+@chelt+''',SumaDebit)) Cheltuieli 
		FROM 
			(SELECT SUM(coalesce(F.ValoareLei,0)) sumaDebit, F.ContDebit 
			FROM ['+@arh+'RulajeDoc] D INNER JOIN ['+@arh+'Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContDebit<>''N/A''  AND  dbo.IsContIn('',''+ dbo.GetSintetic(F.ContDebit)+'','','''+@toate+''')<>0 '  + @filtData + @strFiltruCapitol +
			' AND D.[CodUnitate] = '+@strCodUnitate+@filtruArhRulajeDoc+
		' GROUP BY F.ContDebit) t1 
		FULL OUTER JOIN 
			(SELECT SUM(coalesce(F.ValoareLei,0)) sumaCredit, F.ContCredit
			FROM ['+@arh+'RulajeDoc] D INNER JOIN ['+@arh+'Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContCredit <>''N/A'' AND  dbo.IsContIn('',''+ dbo.GetSintetic(F.ContCredit)+'','','''+@toate+''')<>0 ' + @filtDataC + @strFiltruCapitol + ' 
			AND D.[CodUnitate] = '+@strCodUnitate+@filtruArhRulajeDoc+'
			 GROUP BY F.ContCredit) t2 
		ON t1.ContDebit = t2.ContCredit 
GROUP BY dbo.GetAnalitic(COALESCE(t2.ContCredit, t1.ContDebit))) RUL
ON  cnt.cont=rul.cont
WHERE   a.ArtAl <>''N/A'' ' + @filtC

SET @strSelect = @strSelect + ' GROUP BY  a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire,a.Denumire,a.TermDenumire,a.Anexa6,a.Terminal,a.Titlu' 


--ON substring(rul.cont,1,len(c.cont))=c.cont


PRINT @strSelect
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE				

UPDATE [#balanta_t] SET [CrediteAprobate] = (SELECT SUM([CrediteAprobate]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[CrediteDeschise] = (SELECT SUM([CrediteDeschise]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[AngajamenteBugetare] = (SELECT SUM([AngajamenteBugetare]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[AngajamenteLegale] = (SELECT SUM([AngajamenteLegale]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[PlatiNete] = (SELECT SUM([PlatiNete]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[DisponibilCredit] = (SELECT SUM([DisponibilCredit]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl),
[Cheltuieli] = (SELECT SUM([Cheltuieli]) FROM [#balanta_t] a WHERE a.AAParinte=[#balanta_t].ArtAl)
WHERE [Categorii]='[TOT]'


SET @strSelect ='INSERT INTO [#balanta_t]
SELECT ''0'',''0'',''0'',''0'',1,0,''TOTAL'', 
	SUM(COALESCE([CrediteAprobate],0)),
	SUM(COALESCE([CrediteDeschise],0)),
	SUM(COALESCE([AngajamenteBugetare],0)),
	SUM(COALESCE([AngajamenteLegale],0)),
	SUM(COALESCE([PlatiNete],0)),
	SUM(COALESCE([DisponibilCredit],0)),
	SUM(COALESCE([Cheltuieli],0)),
	SUM(COALESCE([DebitInitial],0)),
	SUM(COALESCE([CreditInitial],0))
FROM [#balanta_t] '

SET @strSelect=@strSelect + 'WHERE terminal=1'
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE	

if @centr=0
BEGIN
	if @cfm=0
	SELECT [ArtAl], 	[Denumire],[Terminal],[Titlu],[CrediteAprobate],[CrediteDeschise],[AngajamenteBugetare],[AngajamenteLegale],[PlatiNete],[DisponibilCredit],[Cheltuieli] FROM [#balanta_t] 
	WHERE [CrediteAprobate] <>0 OR [AngajamenteBugetare]<>0 OR [AngajamenteLegale]<>0 OR [CrediteDeschise] <> 0 OR [PlatiNete] <> 0 OR [Cheltuieli] != 0   
	ORDER BY ArtAl
	else
	SELECT [ArtAl], 	[Denumire],[Terminal],[Titlu],[CrediteAprobate],[CrediteDeschise],[AngajamenteBugetare],[AngajamenteLegale],[PlatiNete],[DisponibilCredit],[Cheltuieli] FROM [#balanta_t]
	ORDER BY ArtAl
END
ELSE
SELECT [ArtAl], 	[Denumire],[Terminal],[Titlu],[CrediteAprobate],[CrediteDeschise],[AngajamenteBugetare],[AngajamenteLegale],[PlatiNete],[DisponibilCredit],[Cheltuieli] FROM [#balanta_t]
WHERE [Categorii]='[TOT]' OR Anexa6=1   
ORDER BY ArtAl

GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balantei',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('Va rugam completati capitolul pentru lista curenta!!',16,-1)	
	GOTO Gata
Gata: